import pandas as pd
from openpyxl import load_workbook
import tkinter as tk
from tkinter import filedialog

# 创建一个隐藏的Tkinter根窗口
root = tk.Tk()
root.withdraw()

# 弹出文件选择对话框
file_path = filedialog.askopenfilename(title="选择要处理的项目工时统计文件", filetypes=[("Excel files", "*.xlsx")])

if not file_path:
    print("未选择文件，程序退出。")
    exit()

sheet_name = '项目工时归档'
columns_to_extract = [1, 4, 5]  # 第2, 5, 6列的索引

df = pd.read_excel(file_path, sheet_name=sheet_name, usecols=columns_to_extract)

# 对数据进行分组和聚合
grouped_df = df.groupby(['项目编号', '所属部门'])['本次用工工时'].sum().reset_index()

# 保存分组聚合后的数据到中间文件
grouped_df.to_excel('grouped_data.xlsx', index=False)

# 使用pivot方法将数据转换为行列索引的形式
pivot_df = grouped_df.pivot(index='项目编号', columns='所属部门', values='本次用工工时')

# 保存转换后的数据到中间文件
pivot_df.to_excel('pivoted_data.xlsx')

# 移除行标签名
pivot_df.index.name = None

# 保存转换后的数据到新的Excel文件
output_file_path = '转换后的项目工时数据.xlsx'
pivot_df.to_excel(output_file_path, merge_cells=False)

# 加载生成的Excel文件
wb = load_workbook(output_file_path)
ws = wb.active

# 调整第一行的单元格宽度
for cell in ws[1]:
    ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                         len(str(cell.value)) + 2)

# 调整第一列的单元格宽度
for row in ws.iter_rows(min_row=2, max_col=1):
    for cell in row:
        ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                             len(str(cell.value)) + 2)

# 保存调整后的Excel文件
wb.save(output_file_path)

# 定义部门和地区映射关系
region_mapping = {
    'BA品质管理': 'SH',
    'HW设计': 'SH',
    'LF阀门生产': 'SH',
    'SW设计': 'SH',
    '仓库管理': 'SH',
    '制造科': 'SH',
    '工程部上海': 'SH',
    '工程部无锡': 'SH',
    '工程部苏州': 'SH',
    '盘制造': 'SH',
    '盘检查': 'SH',
    '工程部北京': 'BJ',
    '工程部大连': 'BJ',
    '工程部天津': 'BJ',
    '工程部深圳': 'GZ'
}

# 筛选合同号包含“BA”和“LF”或者包含“BA”和“PD”的数据
filtered_df_lf_pd = pivot_df[
    pivot_df.index.str.contains('BA') & (pivot_df.index.str.contains('LF') | pivot_df.index.str.contains('PD'))]

# 保存筛选后的数据到中间文件
filtered_df_lf_pd.to_excel('filtered_lf_pd_data.xlsx')

# 将列名转换为地区信息
filtered_df_lf_pd = filtered_df_lf_pd.stack().reset_index()
filtered_df_lf_pd.columns = ['项目编号', '所属部门', 'LF类金额']

# 添加地区信息到每一行数据
filtered_df_lf_pd['地区'] = filtered_df_lf_pd['所属部门'].map(region_mapping)

# 对每个地区的数据进行汇总
summed_df_lf_pd = filtered_df_lf_pd.groupby('地区')['LF类金额'].sum().reset_index()

# 修改地区的值，在每个地区的值后面加上“LF-2024001”
summed_df_lf_pd['地区'] = summed_df_lf_pd['地区'] + '-LF-2024001'

# 读取原始Excel文件
output_file_path = '转换后的项目工时数据.xlsx'
wb = load_workbook(output_file_path)
ws = wb.active

# 找到最后一行
last_row = ws.max_row + 1

# 写入数据，跳过标题行
for index, row in summed_df_lf_pd.iterrows():
    # 将“地区”写入第一列
    ws.cell(row=last_row, column=1, value=row['地区'])
    # 将“LF类金额”写入第18列
    ws.cell(row=last_row, column=18, value=row['LF类金额'])
    last_row += 1

# 保存调整后的Excel文件
wb.save(output_file_path)

# 筛选只包含“BA”但是不包含“LF”和“PD”的合同号的数据
filtered_df_ba_only = pivot_df[
    pivot_df.index.str.contains('BA') & ~pivot_df.index.str.contains('LF') & ~pivot_df.index.str.contains('PD')]

# 对每个部门的数据进行汇总
summed_df_ba_only = filtered_df_ba_only.sum(axis=0).reset_index()
summed_df_ba_only.columns = ['所属部门', '公共合同号金额']

# 保存汇总后的数据到中间文件
summed_df_ba_only.to_excel('summed_ba_only_data.xlsx', index=False)

# 行列转换
transposed_df = summed_df_ba_only.set_index('所属部门').T

# 重置索引
transposed_df.reset_index(inplace=True)

# 删除第一列（索引列为0的列）
transposed_df = transposed_df.drop(columns=transposed_df.columns[0])

# 筛选剩余不包含“BA”的普通合同号，并计算金额不为0的普通合同号
filtered_df_others = pivot_df[~pivot_df.index.str.contains('BA')]
filtered_df_others = filtered_df_others[filtered_df_others.sum(axis=1) != 0]

# 保存处理后的普通合同号数据到中间文件
filtered_df_others.to_excel('processed_others_data.xlsx', index=False)

# 提取前6个字符
filtered_df_others['前6字符'] = filtered_df_others.index.str[:6]

# 根据前6个字符进行分组，并对其他列进行求和
grouped_df_others = filtered_df_others.groupby('前6字符').sum().reset_index()

# 重新构建第一列
grouped_df_others['项目编号'] = grouped_df_others['前6字符'] + '2024001'

# 删除临时列
grouped_df_others.drop(columns=['前6字符'], inplace=True)

# 设置新的索引
grouped_df_others.set_index('项目编号', inplace=True)

# 计算每一列的总和（不包括0）
column_sums = grouped_df_others.sum()

# 初始化字典值来存储分配结果
allocation_dict = {}

# 遍历每一列
for col in grouped_df_others.columns:
    # 获取该列不为0的值
    non_zero_values = grouped_df_others[grouped_df_others[col] != 0][col]

    # 如果该列没有不为0的值，跳过
    if non_zero_values.empty:
        continue

    # 计算该列不为0的值的总和
    total_sum = column_sums[col]

    # 计算每个不为0的值占总和的比例
    proportions = non_zero_values / total_sum

    # 获取 transposed_df 中对应列的值
    transposed_value = transposed_df[col].iloc[0]

    # 计算每个不为0的值应分配的值
    allocations = proportions * transposed_value

    # 将分配结果存储到字典中
    allocation_dict[col] = allocations

# 更新 grouped_df_others 中的值
for col in allocation_dict:
    for idx, value in allocation_dict[col].items():
        grouped_df_others.at[idx, col] = round(value, 2)

# 将 grouped_df_others 中的 0 值替换为 None
grouped_df_others = grouped_df_others.replace(0, None)

# 过滤掉除了第一列之外其他列都为空的行
grouped_df_others = grouped_df_others.dropna(how='all', subset=grouped_df_others.columns[1:])

# 将 grouped_df_others 写入到 '转换后的项目工时数据.xlsx' 文件中
output_file_path = '转换后的项目工时数据.xlsx'
wb = load_workbook(output_file_path)
ws = wb.active

# 找到最后一行
last_row = ws.max_row + 1

# 将索引列转换为普通列
grouped_df_others_reset = grouped_df_others.reset_index()

# 写入数据，保留第一列
for row_num, row in enumerate(grouped_df_others_reset.itertuples(index=False), last_row):
    for col_num, value in enumerate(row, 1):
        ws.cell(row=row_num, column=col_num, value=value)

# 保存调整后的Excel文件
wb.save(output_file_path)

# 读取现有的 Excel 文件内容
existing_df = pd.read_excel(output_file_path)

# 删除第一列数据包含“BA”的行
existing_df = existing_df[~existing_df.iloc[:, 0].astype(str).str.contains('BA')]

# 将所有的PD替换为LF
existing_df = existing_df.replace('PD', 'LF', regex=True)

# 对第一列中重复的数据进行合并，其余列的值相加
existing_df = existing_df.groupby(existing_df.columns[0], as_index=False).sum()

output_file_path2 = '未合并的数据.xlsx'
existing_df.to_excel(output_file_path2, index=False)

# 合并existing_df中列名包含“工程部”的列
engineering_columns = [col for col in existing_df.columns if '工程部' in col]
existing_df['工程'] = existing_df[engineering_columns].sum(axis=1)

# 检查是否存在“BA技术本部”列
if 'BA技术本部' in existing_df.columns:
    # 将“BA技术本部”列的值加到“工程”列
    existing_df['工程'] += existing_df['BA技术本部']
    # 删除“BA技术本部”列
    existing_df.drop(columns=['BA技术本部'], inplace=True)

# 删除合并的列
existing_df.drop(columns=engineering_columns, inplace=True)

# 合并existing_df中列名包含“制造”的列（排除“制造部”）以及"盘检查"和"仓库管理"列
manufacturing_columns = [col for col in existing_df.columns if '制造' in col and col != '制造部'] + ['盘检查', '仓库管理']
existing_df['制造'] = existing_df[manufacturing_columns].sum(axis=1)

# 将“制造部”的值加到“HW设计”列中
existing_df['HW设计'] = existing_df.get('HW设计', 0) + existing_df.get('制造部', 0)

existing_df.drop(columns=['制造部'], inplace=True)


# 删除合并的列
existing_df.drop(columns=manufacturing_columns, inplace=True)

# 保存调整后的数据到新的Excel文件
output_file_path1 = '中间数据.xlsx'
existing_df.to_excel(output_file_path1, index=False)

lf_valve_columns = ['LF阀门生产', 'Unnamed: 17']
existing_columns = [col for col in lf_valve_columns if col in existing_df.columns]

if 'Unnamed: 17' not in existing_df.columns:
    print("Warning: 'Unnamed: 17' column not found.")
    existing_columns = ['LF阀门生产']

existing_df[existing_columns] = existing_df[existing_columns].fillna(0)

# 删除“BA品质管理”列
if 'BA品质管理' in existing_df.columns:
    existing_df.drop(columns=['BA品质管理'], inplace=True)

# 将第一列的列名改为空
existing_df.columns.values[0] = '合同号'

# 在最后一列新增一个“合计”列，对除了第一列的其余列进行求和
existing_df['合计'] = existing_df.iloc[:, 1:].sum(axis=1)

# 将所有为0的单元格替换为空
existing_df = existing_df.replace(0, '')

# 保存调整后的数据到新的Excel文件
output_file_path = '转换后的项目工时数据.xlsx'
existing_df.to_excel(output_file_path, index=False)

# 加载生成的Excel文件
wb = load_workbook(output_file_path)
ws = wb.active

# 对数据进行排序，将第一列含有 '2024001' 的数据放在最上面
existing_df = pd.read_excel(output_file_path)
existing_df = existing_df.sort_values(by=existing_df.columns[0], key=lambda x: x.str.contains('2024001'), ascending=False)
existing_df.to_excel(output_file_path, index=False)

# 重新加载生成的Excel文件
wb = load_workbook(output_file_path)
ws = wb.active

# 调整第一行的单元格宽度
for cell in ws[1]:
    ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                         len(str(cell.value)) + 2)

# 调整第一列的单元格宽度
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=1):
    for cell in row:
        ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                             len(str(cell.value)) + 2)

# 保存调整后的Excel文件
wb.save(output_file_path)
